#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "



--日维度

INSERT INTO hive.online_edu_dws.dws_apply_day_table
WITH temp AS
(SELECT
    --维度
    dt,
    origin_type,
    CASE
        WHEN origin_type = 'NETSERVICE' THEN 'online' --代表线上
        WHEN origin_type = 'PRESIGNUP' THEN 'online' --代表线上
        ELSE 'offline' --其它都是线下
    END AS origin_type_new,
    itcast_school_id,
    itcast_school_name, --校区
    itcast_subject_id,
    itcast_subject_name, --学科
    origin_channel, --渠道
    tdepart_id,
    tdepart_name, --咨询中心
    --指标
    customer_id, --客户id
    --判断指标
    payment_state, --用于判断是否已支付
    appeal_status, -- 用于判断是否线索有效
    --去重判断
    row_number() over(PARTITION BY customer_id) AS customer_rn,
    row_number() over(PARTITION BY customer_id, itcast_school_id) AS school_rn,
    row_number() over(PARTITION BY customer_id, origin_type) AS type_rn,
    row_number() over(PARTITION BY customer_id, origin_type, itcast_school_id) AS type_school_rn,
    row_number() over(PARTITION BY customer_id, origin_type, itcast_subject_id) AS type_subject_rn,
    row_number() over(PARTITION BY customer_id, origin_type, itcast_school_id, itcast_subject_id) AS type_school_subject_rn,
    row_number() over(PARTITION BY customer_id, origin_type, origin_channel) AS type_channel_rn,
    row_number() over(PARTITION BY customer_id, origin_type, tdepart_id) AS type_tdepart_rn
FROM hive.online_edu_dwb.dwb_apply_wide_table)
SELECT
    --维度
    origin_type_new,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    origin_channel,
    tdepart_id,
    tdepart_name,
    --分组标记
    CASE
        --线上线下+校区+学科
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name) = 0 THEN 'type_school_subject'
        --线上线下+学科
        WHEN GROUPING (origin_type_new,itcast_subject_id,itcast_subject_name) = 0 THEN 'type_subject'
        --线上线下+校区
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name) = 0 THEN 'type_school'
        --校区
        WHEN GROUPING (itcast_school_id,itcast_school_name) = 0 THEN 'school'
        --线上线下+渠道来源
        WHEN GROUPING (origin_type_new,origin_channel) = 0 THEN 'type_channel'
        --线上线下+咨询中心
        WHEN GROUPING (origin_type_new,tdepart_id,tdepart_name) = 0 THEN 'type_tdepart'
        --线上线下
        WHEN GROUPING (origin_type_new) = 0 THEN 'type'
        ELSE 'other'
    END AS group_type,
    --指标统计
        --报名人数
    CASE
        --日期+线上线下+校区+学科
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name) = 0
        THEN sum (if(type_school_subject_rn = 1 AND origin_type_new IS NOT NULL AND itcast_school_id IS NOT NULL AND itcast_subject_id IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0))
        --日期+线上线下+学科
        WHEN GROUPING (origin_type_new,itcast_subject_id,itcast_subject_name) = 0
        THEN SUM (if(type_subject_rn = 1 AND origin_type_new IS NOT NULL AND itcast_subject_id IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0 ))
        --日期+线上线下+校区
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name) = 0
        THEN SUM (if(type_school_rn = 1 AND origin_type_new IS NOT NULL AND itcast_school_id IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0))
        --日期+校区
        WHEN GROUPING (itcast_school_id,itcast_school_name) = 0
        THEN SUM (if(school_rn = 1 AND itcast_school_id IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0))
        --日期+线上线下+渠道来源
        WHEN GROUPING (origin_type_new,origin_channel) = 0
        THEN SUM (if(type_channel_rn = 1 AND origin_type_new IS NOT NULL AND origin_channel IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0))
        --日期+线上线下+咨询中心
        WHEN GROUPING (origin_type_new,tdepart_id,tdepart_name) = 0
        THEN SUM (if(type_tdepart_rn = 1 AND origin_type_new IS NOT NULL AND tdepart_id IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0))
        --日期+线上线下
        WHEN GROUPING (origin_type_new) = 0
        THEN SUM (if(type_rn = 1 AND origin_type_new IS NOT NULL AND customer_id IS NOT NULL AND payment_state='PAID', 1, 0))
        ELSE NULL
    END AS apply_cnt,

        --意向人数
    CASE
        --日期+线上线下+校区+学科
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name) = 0
        THEN sum (if(type_school_subject_rn = 1 AND origin_type_new IS NOT NULL AND itcast_school_id IS NOT NULL AND itcast_subject_id IS NOT NULL AND customer_id IS NOT NULL, 1, 0))
        --日期+线上线下+学科
        WHEN GROUPING (origin_type_new,itcast_subject_id,itcast_subject_name) = 0
        THEN SUM (if(type_subject_rn = 1 AND origin_type_new IS NOT NULL AND itcast_subject_id IS NOT NULL AND customer_id IS NOT NULL, 1, 0 ))
        --日期+线上线下+校区
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name) = 0
        THEN SUM (if(type_school_rn = 1 AND origin_type_new IS NOT NULL AND itcast_school_id IS NOT NULL AND customer_id IS NOT NULL, 1, 0))
        --日期+校区
        WHEN GROUPING (itcast_school_id,itcast_school_name) = 0
        THEN SUM (if(school_rn = 1 AND itcast_school_id IS NOT NULL AND customer_id IS NOT NULL, 1, 0))
        --日期+线上线下+渠道来源
        WHEN GROUPING (origin_type_new,origin_channel) = 0
        THEN SUM (if(type_channel_rn = 1 AND origin_type_new IS NOT NULL AND origin_channel IS NOT NULL AND customer_id IS NOT NULL, 1, 0))
        --日期+线上线下+咨询中心
        WHEN GROUPING (origin_type_new,tdepart_id,tdepart_name) = 0
        THEN SUM (if(type_tdepart_rn = 1 AND origin_type_new IS NOT NULL AND tdepart_id IS NOT NULL AND customer_id IS NOT NULL, 1, 0))
        --日期+线上线下
        WHEN GROUPING (origin_type_new) = 0
        THEN SUM (if(type_rn = 1 AND origin_type_new IS NOT NULL AND customer_id IS NOT NULL, 1, 0))
        ELSE NULL
    END AS intentional_cnt,

     --有效线索人数
    CASE
        --日期+线上线下+校区+学科
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name) = 0
        THEN sum (if(type_school_subject_rn = 1 AND origin_type_new IS NOT NULL AND itcast_school_id IS NOT NULL AND itcast_subject_id IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0))
        --日期+线上线下+学科
        WHEN GROUPING (origin_type_new,itcast_subject_id,itcast_subject_name) = 0
        THEN SUM (if(type_subject_rn = 1 AND origin_type_new IS NOT NULL AND itcast_subject_id IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0 ))
        --日期+线上线下+校区
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name) = 0
        THEN SUM (if(type_school_rn = 1 AND origin_type_new IS NOT NULL AND itcast_school_id IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0))
        --日期+校区
        WHEN GROUPING (itcast_school_id,itcast_school_name) = 0
        THEN SUM (if(school_rn = 1 AND itcast_school_id IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0))
        --日期+线上线下+渠道来源
        WHEN GROUPING (origin_type_new,origin_channel) = 0
        THEN SUM (if(type_channel_rn = 1 AND origin_type_new IS NOT NULL AND origin_channel IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0))
        --日期+线上线下+咨询中心
        WHEN GROUPING (origin_type_new,tdepart_id,tdepart_name) = 0
        THEN SUM (if(type_tdepart_rn = 1 AND origin_type_new IS NOT NULL AND tdepart_id IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0))
        --日期+线上线下
        WHEN GROUPING (origin_type_new) = 0
        THEN SUM (if(type_rn = 1 AND origin_type_new IS NOT NULL AND customer_id IS NOT NULL AND appeal_status = 2, 1, 0))
        ELSE NULL
    END AS valid_cnt,
    dt ---做分区
FROM temp
GROUP BY GROUPING SETS (
    (dt,itcast_school_id,itcast_school_name), --日期+校区
    (dt,origin_type_new), --日期+线上线下
    (dt,origin_type_new,itcast_school_id,itcast_school_name), --日期+线上线下+校区
    (dt,origin_type_new,itcast_subject_id,itcast_subject_name), --日期+线上线下+学科
    (dt,origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name), --日期+线上线下+校区+学科
    (dt,origin_type_new,origin_channel), --日期+线上线下+渠道来源
    (dt,origin_type_new,tdepart_id,tdepart_name) --日期+线上线下+咨询中心
);

----------------------------------------------------------------------
--上卷到年月日
INSERT INTO hive.online_edu_dws.dws_apply_year_month_day_table
WITH day_tmp AS
(SELECT
    --时间维度
    SUBSTRING (dt,1,4) AS year_code,
    SUBSTRING (dt,6,2) AS month_code,
    SUBSTRING (dt,1,7) AS year_month,
    SUBSTRING (dt,9,2) AS day_code,
    dt AS year_day,
    --维度
    origin_type_new,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    origin_channel,
    tdepart_id,
    tdepart_name,
    group_type,
    apply_cnt,
    intentional_cnt,
    valid_cnt
FROM hive.online_edu_dws.dws_apply_day_table),
year_month_day_tmp AS
(SELECT
    --统计日期
    '2023-05-07' AS date_time,
    --时间维度
    year_code,
    month_code,
    year_month,
    day_code,
    year_day,
    --时间维度标记
    CASE
        WHEN GROUPING (year_day) = 0 THEN 'day'
        WHEN GROUPING (year_month) = 0 THEN 'month'
        WHEN GROUPING (year_code) = 0 THEN 'year'
        ELSE NULL
    END AS time_type,
    --维度
    origin_type_new,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    origin_channel,
    tdepart_id,
    tdepart_name,
    --维度新标记
    CASE
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name) = 0 THEN 'type_school_subject'
        WHEN GROUPING (origin_type_new,itcast_subject_id,itcast_subject_name) = 0 THEN 'type_subject'
        WHEN GROUPING (origin_type_new,itcast_school_id,itcast_school_name) = 0 THEN 'type_school'
        WHEN GROUPING (itcast_school_id,itcast_school_name) = 0 THEN 'school'
        WHEN GROUPING (origin_type_new,origin_channel) = 0 THEN 'type_channel'
        WHEN GROUPING (origin_type_new,tdepart_id,tdepart_name) = 0 THEN 'type_tdepart'
        WHEN GROUPING (origin_type_new) = 0 THEN 'type'
        ELSE 'other'
    END AS group_type_new,
    --旧的标记
    group_type AS group_type_old,
    --指标统计
    sum(apply_cnt) AS apply_cnt,
    sum(intentional_cnt) AS intentional_cnt,
    sum(valid_cnt) AS valid_cnt
FROM day_tmp
GROUP BY GROUPING SETS (
    --年
    (year_code,itcast_school_id,itcast_school_name,group_type), --年+校区
    (year_code,origin_type_new,group_type), --日期+线上线下
    (year_code,origin_type_new,itcast_school_id,itcast_school_name,group_type), --年+线上线下+校区
    (year_code,origin_type_new,itcast_subject_id,itcast_subject_name,group_type), --年+线上线下+学科
    (year_code,origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,group_type), --年+线上线下+校区+学科
    (year_code,origin_type_new,origin_channel,group_type), --年+线上线下+渠道来源
    (year_code,origin_type_new,tdepart_id,tdepart_name,group_type), --年+线上线下+咨询中心
    --月
    (year_code,month_code,year_month,itcast_school_id,itcast_school_name,group_type), --月+校区
    (year_code,month_code,year_month,origin_type_new,group_type), --月+线上线下
    (year_code,month_code,year_month,origin_type_new,itcast_school_id,itcast_school_name,group_type), --月+线上线下+校区
    (year_code,month_code,year_month,origin_type_new,itcast_subject_id,itcast_subject_name,group_type), --月+线上线下+学科
    (year_code,month_code,year_month,origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,group_type), --月+线上线下+校区+学科
    (year_code,month_code,year_month,origin_type_new,origin_channel,group_type), --月+线上线下+渠道来源
    (year_code,month_code,year_month,origin_type_new,tdepart_id,tdepart_name,group_type),--月+线上线下+咨询中心
    --日
    (year_code,month_code,year_month,day_code,year_day,itcast_school_id,itcast_school_name,group_type), --月+校区
    (year_code,month_code,year_month,day_code,year_day,origin_type_new,group_type), --月+线上线下
    (year_code,month_code,year_month,day_code,year_day,origin_type_new,itcast_school_id,itcast_school_name,group_type), --月+线上线下+校区
    (year_code,month_code,year_month,day_code,year_day,origin_type_new,itcast_subject_id,itcast_subject_name,group_type), --月+线上线下+学科
    (year_code,month_code,year_month,day_code,year_day,origin_type_new,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,group_type), --月+线上线下+校区+学科
    (year_code,month_code,year_month,day_code,year_day,origin_type_new,origin_channel,group_type), --月+线上线下+渠道来源
    (year_code,month_code,year_month,day_code,year_day,origin_type_new,tdepart_id,tdepart_name,group_type)--日+线上线下+咨询中心
))
SELECT
    date_time,
    year_code,
    month_code,
    year_month,
    day_code,
    year_day,
    time_type,
    origin_type_new,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    origin_channel,
    tdepart_id,
    tdepart_name,
    group_type_new,
    apply_cnt,
    intentional_cnt,
    valid_cnt
FROM year_month_day_tmp WHERE group_type_new = group_type_old;


